package cn.kgc.tangcco.tcbd1011.no13.vehicletype.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.kgc.tangcco.tcbd1011.no13.base.dao.BaseDao;
import cn.kgc.tangcco.tcbd1011.no13.base.util.JDBCUtils;
import cn.kgc.tangcco.tcbd1011.no13.vehicletype.dao.VehicleTypeDao;
import cn.kgc.tangcco.tcbd1011.no13.vehicletype.entity.VehicleType;
import cn.kgc.tangcco.tcbd1011.no13.vehicletype.vo.VehicleTypeVo;

public class VehicleTypeDaoImpl extends BaseDao implements VehicleTypeDao {
	
	@Override
	public List<VehicleTypeVo> queryAllVehicleType(VehicleTypeVo vehicleType) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT t.`typeName`,vb.`vehicleBrand`,v.* FROM vehicletype v,TYPE t,vehiclebrand vb WHERE t.`typeId`=v.`typeId` AND v.`vehicleBrandId`=vb.`vehicleBrandId` ");
		String sql = sb.append(" ORDER BY vehicleTypeId DESC").toString();
		List<Object> list = new ArrayList<>();
		try {
			return qr.query(sql, new BeanListHandler<>(VehicleTypeVo.class), list.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<VehicleTypeVo> queryVehicleTypeToVehicle(VehicleTypeVo vehicleType) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT t.`typeName`,vb.`vehicleBrand`,v.* FROM vehicletype v,TYPE t,vehiclebrand vb WHERE t.`typeId`=v.`typeId` AND v.`vehicleBrandId`=vb.`vehicleBrandId` ");
		String sql = sb.append(" ORDER BY vehicleTypeId DESC").toString();
		List<Object> list = new ArrayList<>();
		try {
			return qr.query(sql, new BeanListHandler<>(VehicleTypeVo.class), list.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public VehicleTypeVo detailVehicleType(int pk) {
		String sql = "SELECT t.`typeName`,vb.`vehicleBrand`,v.* FROM vehicletype v,TYPE t,vehiclebrand vb WHERE t.`typeId`=v.`typeId` AND v.`vehicleBrandId`=vb.`vehicleBrandId` AND v.`vehicleTypeId`=? ORDER BY vehicleTypeId DESC";
		try {
			return qr.query(sql, new BeanHandler<>(VehicleTypeVo.class), pk);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int queryCountVehicleType() {
		String sql = "SELECT COUNT(*) FROM vehicletype v,TYPE t,vehiclebrand vb WHERE t.`typeId`=v.`typeId` AND v.`vehicleBrandId`=vb.`vehicleBrandId`";
		try {
			return ((Long)qr.query(sql, new ScalarHandler<>())).intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int addVehicleType(VehicleType vehicleType) {
		return this.executeUpdate(qr, "INSERT INTO vehicletype VALUES(DEFAULT,?,?,?,?)",
				vehicleType.getVehicleBrandId(),vehicleType.getTypeId(),vehicleType.getVehicleTypeName(),vehicleType.getSeat());
	}

	@Override
	public int updateVehicleType(VehicleType vehicleType) {
		
		return this.executeUpdate(qr, "UPDATE vehicletype SET vehicleBrandId=?, TYPEId=? ,vehicleTypeName=?, seat=? WHERE vehicletypeId = ?",
				vehicleType.getVehicleBrandId(),vehicleType.getTypeId(),vehicleType.getVehicleTypeName(),vehicleType.getSeat(),vehicleType.getVehicleTypeId());
	}

	@Override
	public int deleteVehicleType(int pk) {
		QueryRunner queryRunner = new QueryRunner();
		Connection conn = null;
		conn = JDBCUtils.getConnection();
		try {
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(conn.TRANSACTION_REPEATABLE_READ);
			String delete_vehicle_sql = "DELETE FROM vehicle WHERE vehicleTypeId = ?";
			queryRunner.update(conn, delete_vehicle_sql, pk);
			String delete_vehicleType_sql = "delete from vehicleType where vehicleTypeId = ?";
			queryRunner.update(conn, delete_vehicleType_sql, pk);
			conn.commit();
			return 1;
		} catch (SQLException e) {
			if(conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		}
		return 0;
	}

}
